﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections.Generic;
using GoShopping.Model;

namespace GoShopping.Web.ProductManager
{
    public partial class ProductIn : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //Master.ElementSelectedChange = GetTreeView;
                //SetDDLPrdtIn();
            }
        }

        private void GetTreeView()
        {
            TreeView tv = Master.MasterTreeView;
            SetControlCommon.GetPrdtTreeView(tv, Server.MapPath("Navigation.xml"), "prdt");
        }

        protected void ExtensionGridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            this.ExtensionGridView1.PageIndex = e.NewPageIndex;
            //GetGVData();
        }

        #region Control Event
        protected void ddlPrdtType_SelectedIndexChanged(object sender, EventArgs e)
        {
            //GetGVData(ddlPrdtType);
        }

        protected void extGVPrdtIn_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "PIEdit")
            {
                Response.Redirect("AddProduct.aspx?ProductID=" + e.CommandArgument);
            }
        }
        #endregion
        protected void btnPrdtSheet_Click(object sender, EventArgs e)
        {
            BLL.PrdctInfo.ExcelHelper eh = new GoShopping.BLL.PrdctInfo.ExcelHelper();
            eh.ExportTemplate(this);
        }
        protected void btnPreview_Click(object sender, EventArgs e)
        {
            if (!FileUpload1.HasFile)
            {
                GoShopping.Utility.MessageBox.Show(this, "您没有选择文件");
                return;
            }
            string[] str = FileUpload1.FileName.Split('.');
            string str1 = str[FileUpload1.FileName.Split('.').Length - 1];
            if (str1 != "xls")
            {
                GoShopping.Utility.MessageBox.Show(this, "文件格式不对,应该使用导出的模板!");
                return;
            }
            BLL.PrdctInfo.ExcelHelper eh = new GoShopping.BLL.PrdctInfo.ExcelHelper();
            ExtensionGridView1.DataSource = eh.ImportResult(this, FileUpload1);
            ExtensionGridView1.DataBind();
        }
        protected void btnPrdtIn_Click1(object sender, EventArgs e)
        {   
            if (!FileUpload1.HasFile)
            {
                GoShopping.Utility.MessageBox.Show(this, "您没有选择文件");
                return;
            }
            string[] str = FileUpload1.FileName.Split('.');
            string str1 = str[FileUpload1.FileName.Split('.').Length - 1];
            if (str1 != "xls")
            {
                GoShopping.Utility.MessageBox.Show(this, "文件格式不对,应该使用导出的模板!");
                return;
            }
            BLL.PrdctInfo.ExcelHelper eh = new GoShopping.BLL.PrdctInfo.ExcelHelper();
            IList<StockProduct> spList = eh.ImportResult(this, FileUpload1);
            BLL.PrdctInfo.StockProductBean spb = new GoShopping.BLL.PrdctInfo.StockProductBean();
            try
            {
                using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required))
                {
                    foreach (StockProduct sp in spList)
                    {
                        spb.AddOnceStock(sp);
                    }
                    scope.Complete();
                }
            }
            catch
            {
                Utility.MessageBox.Show(this, "数据格式失败,无法导入");
            }

        }
}
}

//protected void btnPrdtIn_Click(object sender, EventArgs e)
//{
//    if (FileUpload1.HasFile)
//    {
//        GoShopping.Utility.MessageBox.Show(this, "您没有选择文件");
//        return;
//    }
//    string[] str = FileUpload1.FileName.Split('.');
//    string str1 = str[FileUpload1.FileName.Split('.').Length - 1];
//    if (str1 != "xls")
//    {
//        GoShopping.Utility.MessageBox.Show(this, "文件格式不对,应该使用导出的模板!");
//        return;
//    }
//    BLL.PrdctInfo.ExcelHelper eh = new GoShopping.BLL.PrdctInfo.ExcelHelper();
//    ExtensionGridView1.DataSource=eh.ImportResult(this, FileUpload1);
//    ExtensionGridView1.DataBind();
//    //DataSet ds = GetGVData();
//    //InsertToSqlServer(ds);
//}

//private DataSet GetGVData()
//{
//    DataSet ds = GetDataFromExcel();
//    ExtensionGridView1.DataSource = ds;
//    ExtensionGridView1.DataBind();
//    return ds;
//}

//private DataSet GetDataFromExcel()
//{
//    //Office 2003字符串:string strConn = @"Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + File1.Value + ";Extended Properties='Excel 11.0; HDR=YES; IMEX=2'";
//    string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + File1.Value + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
//    OleDbConnection objConn = new OleDbConnection(strConn);
//    objConn.Open();
//    DataTable schemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//    string strSql = "Select * From [" + schemaTable.Rows[0][2].ToString() + "]";
//    OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
//    OleDbDataAdapter sqlada = new OleDbDataAdapter(objCmd);
//    DataSet ds = new DataSet();
//    sqlada.Fill(ds, "UserInfo");
//    objConn.Close();
//    return ds;
//}

//private static void InsertToSqlServer(DataSet ds)
//{
//    SqlConnection sqlConn = new SqlConnection();
//    sqlConn.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["GoShoppingConnectionString"].ConnectionString;
//    SqlCommand sqlCmd = new SqlCommand();
//    sqlCmd.Connection = sqlConn;
//    sqlConn.Open();
//    foreach (DataRow dr in ds.Tables["UserInfo"].Rows)
//    {
//        //sqlCmd.CommandText = "INSERT INTO UserInfo SELECT '姓名' as name,'年龄' as age,'婚否' as isMarried FROM " + ds.Tables["UserInfo"];
//        //sqlCmd.CommandText = "INSERT INTO UserInfo(name,age,isMarried) Values('" + dr[0] + "'," + Convert.ToInt16(dr[1]) + "," + Convert.ToInt16(dr[2]) + ")";
//        //sqlCmd.ExecuteNonQuery();
//    }
//    sqlConn.Close();
//}
#region ODDS
//foreach (DataRow dr in schemaTable.Rows)
//{
//    DropDownList1.Items.Add(new ListItem(dr[2].ToString().Trim()));
//    string strSql = "Select " + columnTable.Rows[0][3].ToString().Trim() + " From [" + dr[2].ToString().Trim() + "]";
//    OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
//    sqlada.SelectCommand = objCmd;
//    sqlada.Fill(ds, dr[2].ToString().Trim());
//}

//DropDownList1.Items.Add(new ListItem(schemaTable.Rows[0][2].ToString().Trim()));
//string strSql = "Select " + columnTable.Rows[0][3].ToString().Trim() + " From [" + schemaTable.Rows[0][2].ToString() + "]";
//OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
//sqlada.SelectCommand = objCmd;
//sqlada.Fill(ds, schemaTable.Rows[0][2].ToString());
    //public bool TableExist(string tableName) //查看数据库里是否有此表名
    //{
    //    sqlcom = new SqlCommand();
    //    sqlcom.Connection = sqlcon;
    //    sqlcom.CommandType = CommandType.Text;
    //    try
    //    {
    //        sqlcon.Open();
    //        string sql = "select name from sysobjects where type='u'";
    //        sqlcom.CommandText = sql;
    //        SqlDataReader sqldr = sqlcom.ExecuteReader();
    //        while (sqldr.Read())
    //        {
    //            if (sqldr.GetString(0).ToUpper() == tableName.ToUpper())
    //                return true;
    //        }
    //    }
    //    catch { return false; }
    //    finally
    //    {
    //        sqlcon.Close();
    //    }
    //    return false;
    //}
    //public ArrayList GetTableField(string tableName)  //得到数据库某一个表中的所有字段
    //{
    //    ArrayList al = new ArrayList();
    //    sqlcom = new SqlCommand();
    //    sqlcom.Connection = sqlcon;
    //    sqlcom.CommandType = CommandType.Text;
    //    try
    //    {
    //        sqlcon.Open();
    //        string sql = "SELECT b.name FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id WHERE (a.name = '" + tableName + "')";
    //        sqlcom.CommandText = sql;
    //        SqlDataReader sqldr = sqlcom.ExecuteReader();
    //        while (sqldr.Read())
    //        {
    //            al.Add(sqldr.GetString(0));
    //        }
    //    }
    //    finally
    //    {
    //        sqlcon.Close();
    //    }
    //    return al; //返回的是表中的字段
    //}

#region Other SchemaTable
//DataTable schemaTable2 = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//GridView3.DataSource = schemaTable2;
//GridView3.DataBind();

////得到全部的用户表，用户表类型为Table，进行过滤
//DataTable schemaTable3 = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//this.GridView4.DataSource = schemaTable3;
//this.GridView4.DataBind();

////得到全部的视图
//DataTable schemaTable4 = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "VIEW" });
//this.GridView5.DataSource = schemaTable4;
//this.GridView5.DataBind();


////得到全部支持的数据类型
//DataTable schemaTable6 = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Provider_Types, null);
//this.GridView7.DataSource = schemaTable6;
//this.GridView7.DataBind();

////得到全部的主键信息
//DataTable schemaTable7 = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
//this.GridView8.DataSource = schemaTable7;
//this.GridView8.DataBind();
#endregion
#region BackUp
        ////string strConn = @"Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + File1.Value + ";Extended Properties='Excel 11.0; HDR=YES; IMEX=2'";
        //string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + File1.Value + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=2'";
        //OleDbConnection objConn = new OleDbConnection(strConn);
        //objConn.Open();
        //DataTable schemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        //OleDbDataAdapter sqlada = new OleDbDataAdapter();
        //DataSet ds = new DataSet();
        //foreach (DataRow dr in schemaTable.Rows)
        //{
        //    string strSql = "Select * From [" + dr[2].ToString().Trim() + "]";
        //    OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
        //    sqlada.SelectCommand = objCmd;
        //    sqlada.Fill(ds, dr[2].ToString().Trim());
        //}
        //GridView1.DataSource = ds;
        //GridView1.DataBind();
        //objConn.Close();
#endregion
#region
////这是给出了两个连接字符串，一个是xls,第二个是xlsx，就是Excel 2007格式。如果需要更多的字符串，可以访问这个网站找答案。Privider就是OLEDB的提供者，这个很常见。DataSource就是文件名，

//ExtendedProperties：

// 一个选项是Excel的版本号，

//一个选项是HDR=YES/No; 就是第一行是不是数据的列名，如果是就自动用这个做Table的列名。如果不是就用A1，A2，F3这些名字。

//一个选项是IMEX=1 就是说数据是不是混排的，叫Intermixed。这样读取时候直接按Text读，不自行根据第一列的数据属性瞎猜。否则，如果第一行是数字，会默认以后这一列全是数字，如果不是数字呢，就给个空值。

//http://www.connectionstrings.com/?carrier=excel2007

////如果连接字符串不对，有可能出现"Could not find installable ISAM ” Exception
//private string GetConnectionString(string fileName)
//{
////xls文件的连接字符串
///*string connectString =
//@" Provider=Microsoft.Jet.OLEDB.4.0;" +
//@" Data Source=" + fileName + ";" +
//@" Extended Properties=" + Convert.ToChar(34).ToString() +
//@" Excel 8.0;" + Convert.ToChar(34).ToString();*/
////xlsx，Excel 2007文件的连接字符串
//string connectString =
//@" Provider=Microsoft.ACE.OLEDB.12.0;" +
//@" Data Source=" + fileName + ";" +
//@" Extended Properties=" + Convert.ToChar(34).ToString() +
//@" Excel 12.0;" + Convert.ToChar(34).ToString();

//return connectString;

//}
#endregion
#endregion